package com.rankeiot.core.sqltoy;

import com.rankeiot.core.Current;
import com.rankeiot.core.util.StringUtil;

import org.sagacity.sqltoy.dao.SqlToyLazyDao;
import org.sagacity.sqltoy.link.Query;
import org.sagacity.sqltoy.model.QueryExecutor;

import java.util.*;

public class Q {
    private StringBuffer sql=new StringBuffer();
    private List<Object> params=new ArrayList<>();
    private byte flag=0;
    private final byte FLAG_WHERE=1;
    private final byte FLAG_AND=2;
    private final byte FLAG_OR=3;
    private final byte FLAG_ORDER=4;

    public Q(){

    }
    public Q(String sql,Object ...params){
        this.append(sql,params);
    }
    public Q select(String ...columns){
        append("SELECT ");
        append(String.join(" ",columns));
        return this;
    }
    public <T,R> Q select(Getter<T,R> ...columns){
        String[] columnNames=new String[columns.length];
        for(int i=0;i<columns.length;i++){
            columnNames[i]=ColumnInfo.getColumnName(columns[i]);
        }
        select(columnNames);
        return this;
    }
    public Q from(String table){
        append(" FROM ");
        append(table);
        return this;
    }
    public <T,R> Q from(Class table){
        from(ColumnInfo.getTableName(table));
        return this;
    }
    public Q where(){
        append(" WHERE");
        flag=FLAG_WHERE;
        return this;
    }
    public Q and(){
        if(flag>0){
            return this;
        }
        append(" AND");
        flag=FLAG_AND;
        return this;
       // sql.replace()
    }
    public Q or(){
        if(flag>0){
            return this;
        }
        append(" OR");
        flag=FLAG_OR;
        return this;
    }
    public <T, R> Q eq(Getter<T, R> column,Object value) {
        return operate("=", column,value);
    }

    public <T, R> Q in(Getter<T, R> column,Object value) {
        return operate("in", column,value);
    }

    public <T, R> Q lt(Getter<T, R> column,Object value) {
        return operate("<", column,value);
    }

    public <T, R> Q gt(Getter<T, R> column,Object value) {
        return operate(">", column,value);
    }

    public <T, R> Q ge(Getter<T, R> column,Object value) {
        return operate(">=", column,value);
    }

    public <T, R> Q le(Getter<T, R> column,Object value) {
        return operate("<=", column,value);
    }

    public <T, R> Q ne(Getter<T, R> column,Object value) {
        return operate("<>", column,value);
    }

    public <T, R> Q like(Getter<T, R> column,Object value) {
        return like(ColumnInfo.getColumnName(column),value);
    }
    public <T, R> Q leftLike(Getter<T, R> column,Object value) {
        return leftLike(ColumnInfo.getColumnName(column),value);
    }
    public <T, R> Q rightLike(Getter<T, R> column,Object value) {
        return rightLike(ColumnInfo.getColumnName(column),value);
    }

    public Q eq(String column,Object value){
        return operate("=",column,value);
    }
    public Q in(String column,Object value){
        return operate("in",column,value);
    }

    public Q lt(String column,Object value){
        return operate("<",column,value);
    }

    public Q gt(String column,Object value){
        return operate(">",column,value);
    }

    public Q ge(String column,Object value){
        return operate(">=",column,value);
    }
    public Q le(String column,Object value){
        return operate("<=",column,value);
    }

    public Q ne(String column,Object value){
        return operate("<>",column,value);
    }
    public Q like(String column,Object value){
        if(value==null){
            return this;
        }
        String rel=value.toString();
        if(rel.startsWith("%")||rel.endsWith("%")){
            return operate("like",column,value);
        }
        return operate("like",column,"%"+value+"%");
    }

    public Q leftLike(String column,Object value){
        if(value==null){
            return this;
        }
        return operate("like",column,value+"%");
    }
    public Q rightLike(String column,Object value){
        if(value==null){
            return this;
        }
        return operate("like",column,"%"+value);
    }
    public <T, R> Q isNUll(Getter<T, R> column){
        String columnName = ColumnInfo.getColumnName(column);
        return isNull(columnName);
    }
    public Q isNull(String column){
        and();
        sql.append(" ");
        sql.append(column);
        sql.append(" IS NULL ");
        flag=0;
        return this;
    }
    public Q notNull(String column){
        and();
        sql.append(" ");
        sql.append(column);
        sql.append(" NOT NULL ");
        flag=0;
        return this;
    }
    public <T, R> Q notNull(Getter<T, R> column){
       String columnName = ColumnInfo.getColumnName(column);
        return notNull(columnName);
    }
    public Q operate(String operator, Getter column,Object value) {
        String columnName = ColumnInfo.getColumnName(column);
        return operate(operator,columnName,value);
    }
    public Q operate(String operator,String column,Object value) {
        if(value==null){
            return this;
        }
        and();
        operator = operator.toUpperCase();
        sql.append(" ");
        sql.append(column);
        sql.append(" ");
        if ("IN".equals(operator)) {
            sql.append("IN (?)");
            if(value instanceof String){
                params.add(value.toString().split(","));
            }
        } else {
           sql.append(operator);
           sql.append(" ?");
           params.add(value);
        }
        flag=0;
        return this;
    }

    public <T,R> Q append(Getter<T,R> column){
        this.sql.append(ColumnInfo.getColumnName(column));
        flag=0;
        return this;
    }
    public Q append(String sql,Object ...params){
        this.sql.append(sql);
        this.params.addAll(Arrays.asList(params));
        flag=0;
        return this;
    }
    /**
     * 数据范围判断SQL，有对应字段时，自动拼接上对应查询条件
     * @param user 用户字段
     * @param branch 部门字段
     * @param role 角色字段
     * @return Q
     */
    public Q withScope(String user, String branch, String role){
        String scope = ScopeExecutor.getScope(Current.user(),user, branch, role);
        if(StringUtil.isEmpty(scope)){
            return this;
        }
        and();
        append(scope);
        flag=0;
        return this;
    }
    public <T,R> Q orderBy(Getter<T,R> ...columns){
        StringBuffer orderBy=new StringBuffer();
        for(Getter<T,R> column:columns){
            String columnName = ColumnInfo.getColumnName(column);
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            orderBy.append(columnName);
        }
        if(orderBy.length()==0){
            return this;
        }
        if(flag!=FLAG_ORDER){
            append(" ORDER BY ");
        }else{
            append(",");
        }
        append(orderBy.toString());
        flag=FLAG_ORDER;
        return this;
    }
    public <T,R> Q orderByDesc(Getter<T,R> ...columns){
        StringBuffer orderBy=new StringBuffer();
        for(Getter<T,R> column:columns){
            String columnName = ColumnInfo.getColumnName(column);
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            orderBy.append(columnName);
            orderBy.append(" DESC");
        }
        if(orderBy.length()==0){
            return this;
        }
        if(flag!=FLAG_ORDER){
            append(" ORDER BY ");
        }else{
            append(",");
        }
        append(orderBy.toString());
        flag=FLAG_ORDER;
        return this;
    }

    public Query query(SqlToyLazyDao dao){
       return dao.query().sql(getSql()).values(params.toArray());
    }
    public QueryExecutor query(){
        QueryExecutor qe= new QueryExecutor(getSql());
        qe.values(params.toArray());
        return qe;
    }
    public String getSql(){
        String sql=this.sql.toString();
        if(flag==FLAG_WHERE){
            sql=sql.substring(0,sql.length()-5);
        }else if(flag==FLAG_AND){
            sql=sql.substring(0,sql.length()-3);
        }else if(flag==FLAG_OR){
            sql=sql.substring(0,sql.length()-2);
        }
        return sql;
    }
}
